﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace NutriFast
{
    class dbTavo
    {
        //Conection String
        public static string connString = connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        
        
        public static void alimentoAdd(Alimento a)
        {
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoAlimentoAdd", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandTimeout = 180;
            sqlCommand.Parameters.AddWithValue("@fName", a.fName);
            sqlCommand.Parameters.AddWithValue("@fGroup", a.fGroup);
            sqlCommand.Parameters.AddWithValue("@csugerida", a.csugerida);
            sqlCommand.Parameters.AddWithValue("@unidad", a.unidad);
            sqlCommand.Parameters.AddWithValue("@pbruto", a.pbruto);
            sqlCommand.Parameters.AddWithValue("@pneto", a.pneto);
            sqlCommand.Parameters.AddWithValue("@kcal", a.kcal);
            sqlCommand.Parameters.AddWithValue("@kj", a.kj);
            sqlCommand.Parameters.AddWithValue("@proteinas", a.proteinas);
            sqlCommand.Parameters.AddWithValue("@lipidos", a.lipidos);
            sqlCommand.Parameters.AddWithValue("@hidratosdcarbon", a.hidratosdcarbon);
            sqlCommand.Parameters.AddWithValue("@vitaminaA", a.vitaminaA);
            sqlCommand.Parameters.AddWithValue("@acidoascorbico", a.acidoascorbico);
            sqlCommand.Parameters.AddWithValue("@acidofolico", a.acidofolico);
            sqlCommand.Parameters.AddWithValue("@hierronohem", a.hierronohem);
            sqlCommand.Parameters.AddWithValue("@potasio", a.potasio);
            sqlCommand.Parameters.AddWithValue("@indiceglicemico", a.indiceglicemico);
            sqlCommand.Parameters.AddWithValue("@cargaglicemica", a.cargaglicemica);
            sqlCommand.Parameters.AddWithValue("@azucar", a.azucar);
            sqlCommand.Parameters.AddWithValue("@calcio", a.calcio);
            sqlCommand.Parameters.AddWithValue("@hierro", a.hierro);
            sqlCommand.Parameters.AddWithValue("@sodio", a.sodio);
            sqlCommand.Parameters.AddWithValue("@azucarg", a.azucarg);
            sqlCommand.Parameters.AddWithValue("@selenio", a.selenio);
            sqlCommand.Parameters.AddWithValue("@fosforo", a.fosforo);
            sqlCommand.Parameters.AddWithValue("@colesterol", a.colesterol);
            sqlCommand.Parameters.AddWithValue("@agsaturados", a.agsaturados);
            sqlCommand.Parameters.AddWithValue("@agmono", a.agmono);
            sqlCommand.Parameters.AddWithValue("@agpoli", a.agpoli);
            sqlCommand.Parameters.AddWithValue("@etanol", a.etanol);
            sqlCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static void alimentoUpdate(Alimento a,int idF)
        {
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoAlimentoUpdate", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandTimeout = 180;
            sqlCommand.Parameters.AddWithValue("@idFood", idF);
            sqlCommand.Parameters.AddWithValue("@fName", a.fName);
            sqlCommand.Parameters.AddWithValue("@fGroup", a.fGroup);
            sqlCommand.Parameters.AddWithValue("@csugerida", a.csugerida);
            sqlCommand.Parameters.AddWithValue("@unidad", a.unidad);
            sqlCommand.Parameters.AddWithValue("@pbruto", a.pbruto);
            sqlCommand.Parameters.AddWithValue("@pneto", a.pneto);
            sqlCommand.Parameters.AddWithValue("@kcal", a.kcal);
            sqlCommand.Parameters.AddWithValue("@kj", a.kj);
            sqlCommand.Parameters.AddWithValue("@proteinas", a.proteinas);
            sqlCommand.Parameters.AddWithValue("@lipidos", a.lipidos);
            sqlCommand.Parameters.AddWithValue("@hidratosdcarbon", a.hidratosdcarbon);
            sqlCommand.Parameters.AddWithValue("@vitaminaA", a.vitaminaA);
            sqlCommand.Parameters.AddWithValue("@acidoascorbico", a.acidoascorbico);
            sqlCommand.Parameters.AddWithValue("@acidofolico", a.acidofolico);
            sqlCommand.Parameters.AddWithValue("@hierronohem", a.hierronohem);
            sqlCommand.Parameters.AddWithValue("@potasio", a.potasio);
            sqlCommand.Parameters.AddWithValue("@indiceglicemico", a.indiceglicemico);
            sqlCommand.Parameters.AddWithValue("@cargaglicemica", a.cargaglicemica);
            sqlCommand.Parameters.AddWithValue("@azucar", a.azucar);
            sqlCommand.Parameters.AddWithValue("@calcio", a.calcio);
            sqlCommand.Parameters.AddWithValue("@hierro", a.hierro);
            sqlCommand.Parameters.AddWithValue("@sodio", a.sodio);
            sqlCommand.Parameters.AddWithValue("@azucarg", a.azucarg);
            sqlCommand.Parameters.AddWithValue("@selenio", a.selenio);
            sqlCommand.Parameters.AddWithValue("@fosforo", a.fosforo);
            sqlCommand.Parameters.AddWithValue("@colesterol", a.colesterol);
            sqlCommand.Parameters.AddWithValue("@agsaturados", a.agsaturados);
            sqlCommand.Parameters.AddWithValue("@agmono", a.agmono);
            sqlCommand.Parameters.AddWithValue("@agpoli", a.agpoli);
            sqlCommand.Parameters.AddWithValue("@etanol", a.etanol);
            sqlCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static DataTable grupo()
        {
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoGroup", conn);
            DataTable ds = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            conn.Close();
            return ds;
        }




        public static void mealAdd(int idMeal,int idDiet,float por)
        {
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoInsertMeal", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandTimeout = 180;
            sqlCommand.Parameters.AddWithValue("@idMeal", idMeal);
            sqlCommand.Parameters.AddWithValue("@idDiet", idDiet);
            sqlCommand.Parameters.AddWithValue("@percentaje", por);
            sqlCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static string searchP(int idPatient)
        {
            string nombre = "";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoSearchPatient", conn);
            sqlCommand.Parameters.AddWithValue("@idPatient", idPatient);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);


            try
            {
                nombre = ("" + ds.Tables[0].Rows[0][1]);

            }
            catch (IndexOutOfRangeException e)
            {
                Console.WriteLine(e.ToString());
            }

            conn.Close();
            return nombre;
        }

        public static string[] searchDiet(int idDiet)
        {
            string[]nombre=new string[4];
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoSearchDiet", conn);
            sqlCommand.Parameters.AddWithValue("@idDiet", idDiet);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);
            try
            {
                nombre[0] = ("" + ds.Tables[0].Rows[0][4]);
                nombre[1] = ("" + ds.Tables[0].Rows[0][5]);
                nombre[2] = ("" + ds.Tables[0].Rows[0][6]);
                nombre[3] = ("" + ds.Tables[0].Rows[0][7]);

            }
            catch (IndexOutOfRangeException e)
            {
                Console.WriteLine(e.ToString());
            }
            conn.Close();
            return nombre;
        }

        public static DataTable searchFood(string filtro)
        {
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spTavoSearchFoodKcalUnits", conn);
            sqlCommand.Parameters.AddWithValue("@fName", filtro);
            DataTable ds = new DataTable();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);
            
            conn.Close();
            return ds;
        }

    }
}
